﻿package component.tree.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import component.tree.entity.Entity;
import component.tree.util.TreeUtil;

public class ComponentDAO implements IComponentDAO {
	//根据数据库表的列名获取对应的enity的属性名称，如：forg_id 对应 orgId
	protected String getEntityFieldName(String fieldName){
		String underLine = "_";//下划线
		fieldName = fieldName.substring(1);
		if(fieldName.indexOf(underLine)==-1){
			return fieldName.toLowerCase();
		}else{//包含下划线
			String fieldName2 = "";
			String fieldNameArr[] = fieldName.split(underLine);
			for(int i=0;i<fieldNameArr.length;i++){
				if(fieldName2.equals("")){
					fieldName2+=fieldNameArr[i].toLowerCase();
				}else{
					fieldName2+=fieldNameArr[i].substring(0,1).toUpperCase()+fieldNameArr[i].substring(1).toLowerCase();
				}
			}
			return fieldName2;
		}
	}
	
	//通过sql查询数据,返回List<Object[]>
	public List findDataBySql(String sql){
		List list = new ArrayList();
		
		IConnectionProvider connectionProvider = ConnectionProviderFactory.getConnectionProvider();
		Connection con = connectionProvider.getConnection();
		
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try{
			pstm = con.prepareStatement(sql);
			rs = pstm.executeQuery();
			
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			
			Object[] objs = null;
			while(rs.next()){
				objs = new Object[columnCount];
				for (int i = 1; i <= columnCount; i++) {
					objs[i-1] = rs.getObject(i);
				}
				list.add(objs);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			connectionProvider.close(rs, pstm);
		}
		return list;
	}
	
	//通过sql查询数据,返回List<Entity>
	public List findDataBySql(String sql,Entity entity){
		List list = new ArrayList();
		
		IConnectionProvider connectionProvider = ConnectionProviderFactory.getConnectionProvider();
		Connection con = connectionProvider.getConnection();
		
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try{
			pstm = con.prepareStatement(sql);
			rs = pstm.executeQuery();
			
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			
			while(rs.next()){
				Entity tempEntity = (Entity)TreeUtil.getObject(entity.getClass().getName());
				for (int i = 1; i <= columnCount; i++) {
					String name = getEntityFieldName(rsmd.getColumnName(i));
					String value = String.valueOf(rs.getObject(i));
					tempEntity.setValue(name, value);
				}
				list.add(tempEntity);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			connectionProvider.close(rs, pstm);
		}
		return list;
	}
	
	//根据sql生成insert语句
	public String createSqlByData(String sql){
		StringBuffer sb = new StringBuffer(1000);
		
		IConnectionProvider connectionProvider = ConnectionProviderFactory.getConnectionProvider();
		Connection con = connectionProvider.getConnection();
		
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try{
			pstm = con.prepareStatement(sql);
			rs = pstm.executeQuery();
			
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			
			int row = 0;
			while(rs.next()){
				sb.append("insert into ").append(rsmd.getTableName(1)).append("(");
				String names = "";
				String values = "";
				for (int i = 1; i <= columnCount; i++) {
					String name = rsmd.getColumnName(i);
					String value = String.valueOf(rs.getObject(i));
					String type = rsmd.getColumnTypeName(i);
					//System.out.println(name + " = " + value + " = " + type);
					names += "," + name;
					if(type.equals("CHAR") || type.equals("VARCHAR")){
						values += ",'" + value + "'"; 
					}else{
						values += "," + value;
					}
				}
				sb.append(names.substring(1)).append(") values(").append(values.substring(1)).append("); \n");
				row++;
				/*
				StringBuffer sb1 = new StringBuffer(100);
				sb1.append("insert into ").append(rsmd.getTableName(1)).append("(" );
				sb1.append(names.substring(1)).append(") values(").append(values.substring(1)).append("); \n");
				System.out.println(sb1.toString());
				*/

			}
			//System.out.println("----------------------- row = " + row);
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			connectionProvider.close(rs, pstm);
		}
		return sb.toString();
	}
	
	
	
	public static void main(String[] args) {
		//单位
		String sql = "select FID,FPARENT_ID,FVALIDATION,FORG_CODE_CST,FORG_NAME,FORDER,FTYPE,FLEVEL,FZT_ID,FYEAR from SMG_TORGANIZATION";
		
		//资金来源
		sql = "select FID,FPARENTID,FCODE,FNAME,FORDER,FTYPE,FLEVEL,FPATH,FSRCTYPE,FZTID,FYEAR from TFM_PB_TFUNDSRC";
		
		//经费类型
		sql = "select FID,FPARENTID,FCODE,FNAME,FORDER,FTYPE,FLEVEL,FPATH,FZTID,FYEAR from TFM_PB_TOUTLAYTYPE";
		
		//功能科目
		sql = "select FID,FPARENTID,FCODE,FNAME,FORDER,FTYPE,FLEVEL,FPATH,FSUBTYPE,FZTID,FYEAR from TFM_PB_TSUBJ";
		
		//收费项目
		sql = "select FID,FPARENTID,FCODE,FNAME,FORDER,FTYPE,FLEVEL,FPATH,FZTID,FYEAR from TFM_PB_TINSUBJ";
		
		ComponentDAO dao = new ComponentDAO();
		String str = dao.createSqlByData(sql);
		System.out.println(str);
	}
}
